package com.neusoft.neuiotms.dcam.fixed.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.neusoft.neuiotms.dcam.fixed.model.Asset;
import com.neusoft.neuiotms.dcam.fixed.model.AssetType;
import com.neusoft.neuiotms.dcam.fixed.model.Product;

public interface AssetRepository extends JpaRepository<Asset, Long>, CustomizedAssetRepository {

    Asset findByCode(String code);

    List<Asset> findByProduct(Product product);

    List<Asset> findByType(AssetType type);

    List<Asset> findByOrgId(String orgId);

    void deleteByIdIn(List<Long> ids);

    /**
     * 按资产分类统计
     *
     * @param belongs
     * @param categories
     * @return
     */
    @Query(value = "select t2.name, t1.count from (select type_id, count(*) as count from neuiot_fixed_asset group by  type_id)t1 left join neuiot_fixed_asset_type t2 on t1.type_id = t2.id order by t1.count desc limit 10", nativeQuery = true)
    List<Map<String, Object>> statsByType();

    /**
     * 按厅局统计
     *
     * @param belongs
     * @param categories
     * @return
     */
    @Query(value = "select submmit_to as name, count(*) as count  from neuiot_fixed_asset group by submmit_to order by count desc limit 10", nativeQuery = true)
    List<Map<String, Object>> statsByInstitution();

    /**
     * 按使用状况统计
     *
     * @param belongs
     * @param categories
     * @return
     */
    @Query(value = "select t2.text as name, t1.count from (select status, count(*) as count  from neuiot_fixed_asset group by status order by count desc limit 10) t1 left join neuiot_dictionary t2 on t1.status = t2.code ", nativeQuery = true)
    List<Map<String, Object>> statsByStatus();

}
